package com.ruoyi.tongji.run;

import com.ruoyi.tongji.run.manager.ManagerFactory;
import com.ruoyi.tongji.utils.DateUtil;
import org.apache.commons.lang3.StringUtils;
import org.beetl.sql.core.*;
import org.beetl.sql.core.db.DBStyle;
import org.beetl.sql.core.db.MySqlStyle;
import org.beetl.sql.ext.DebugInterceptor;

import java.io.File;
import java.util.*;

public class 员工和联络员名下浆员二 {

    public static String day = "2023-12-06";
    public static String dateStrStart = day + " 00:00:00";
    public static String dateStrEnd = day + " 23:59:00";

    public static Date dateRangeStart = DateUtil.getDate(dateStrStart, "yyyy-MM-dd HH:mm:ss",-365);
    public static Date dateRangeEnd = DateUtil.getDate(dateStrEnd, "yyyy-MM-dd HH:mm:ss", -93);

    public static  String dateRange = DateUtil.format(dateRangeStart, "yyyy年MM月dd日") + "至" + DateUtil.format(dateRangeEnd, "yyyy年MM月dd日");
    public static String root = "E:\\文档\\2023\\回访记录\\名下浆员\\" + dateRange;

    public static void main(String[] args) {
        SQLManager sqlManager = ManagerFactory.jiangzhanSqlManager();

        SQLManager taibangSqlManager = ManagerFactory.taibangSqlManager();

        List tongjiList = new ArrayList();

        File rootFile = new File(root);
        rootFile.mkdirs();
        //if(true) return ;
        List memberList = sqlManager.execute(new SQLReady("select * from member", new Object[] {}), HashMap.class);

        List hasRecordDonorList = new ArrayList();

        List mingxiaDonorList = new ArrayList();
        for (Iterator<Map<String, String>> iterator = memberList.iterator(); iterator.hasNext();) {
            Map<String, String> m = iterator.next();
            System.out.println(m.get("name"));
            String name = m.get("name");
            String 科室 = m.get("keshi");

            List<HashMap> donorList = taibangSqlManager.execute(new SQLReady("" +
                    "select '" + name + "' as '归属人',donor.donorNo 浆员编号, donor.donorName,donor.phone 联系电话, area.cname as 区域名称, donor.birthday as 生日, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(donor.birthday)), '%Y')+0 AS 年龄, donor.sex, donor.codeDonorState as 状态 ,donor.donorName as 浆员姓名, DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d')  as 最后采浆日期 from t_bims_donor as donor left join t_bims_code_area as area on area.ccode = donor.codeArea where donor.codeDonorState = 'N' and donor.remark like ? and donor.plasmaDate between ? and ? order by donor.plasmaDate desc"
                    ,new Object[] {"%" + name + "%", DateUtil.format(dateRangeStart,"yyyyMMddHHmmsss"), DateUtil.format(dateRangeEnd,"yyyyMMddHHmmsss"), }), HashMap.class);
            Map data = new HashMap();
            data.put("name",name);
            data.put("dateRange",dateRange);
            data.put("today",DateUtil.format(new Date(), "yyyy-MM-dd"));
            data.put("donorList", donorList);hasRecordDonorList.addAll(donorList);mingxiaDonorList.addAll(donorList);
            data.put("count", donorList.size());
            tongjiList.add(data);

            if (donorList.size() == 0) {
                continue;
            }
            ManagerFactory.createExcel(root,
                    "excelTemplates/名下浆员名单.xls",
                    "\\"+ 科室 + "-" + name + "-名下浆员名单（" + donorList.size() + "）.xls",data);

        }
        List 宣传员List = sqlManager.execute(new SQLReady("select * from 宣传员（包含合并） ", new Object[] {}), HashMap.class);

        for (Iterator<Map<String, String>> iterator = 宣传员List.iterator(); iterator.hasNext();) {
            Map<String, String> m = iterator.next();
            String name = m.get("name");

            List<HashMap> donorList = taibangSqlManager.execute(new SQLReady("select * from (\n" +
                    "select d.donorNo 浆员编号, \n" +
                    "'" + name + "' as '归属人'," +
                    "d.donorName 浆员姓名,\n" +
                    "d.phone 联系电话, area.cname as 区域名称, d.codeDonorState as 状态,\n" +
                    "d.birthday as 生日, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(d.birthday)), '%Y')+0 AS 年龄, d.sex, " +
                    "DATE_FORMAT(c.beginDate,'%Y-%m-%d') as 本次采浆日期, \n" +
                    "DATE_FORMAT(d.plasmaDate,'%Y-%m-%d')  as 最后采浆日期 ,\n" +
                    "d.remark from t_bims_donor_register r left join t_bims_plasma_collection c on r.donorId = c.donorId and LEFT(r.registerDate,8) = LEFT(c.beginDate,8) left join t_bims_donor d on c.donorId = d.donorId left join t_bims_linkman l on r.linkmanId = l.linkmanId left join t_bims_code_area as area on area.ccode = d.codeArea \n" +
                    "where d.plasmaDate between ? and ? and r.registerDate between ? and ? and c.weight > 0 and d.codeDonorState = 'N' and l.linkmanName = ? " +
                    " and (INSTR(d.remark,'翟毅') = 0 and INSTR(d.remark,'潘颖') = 0 and INSTR(d.remark,'张玲') = 0 and INSTR(d.remark,'隗继云') = 0 and INSTR(d.remark,'高芸') = 0 and INSTR(d.remark,'魏永杰') = 0 and INSTR(d.remark,'焦连云') = 0 and INSTR(d.remark,'韩绍丽') = 0 and INSTR(d.remark,'索建芝') = 0 and INSTR(d.remark,'唐琳') = 0 and INSTR(d.remark,'孟淑文') = 0 and INSTR(d.remark,'郭镇') = 0 and INSTR(d.remark,'张加华') = 0 and INSTR(d.remark,'杨树盈') = 0 and INSTR(d.remark,'王敬君') = 0 and INSTR(d.remark,'姜利') = 0 and INSTR(d.remark,'耿鹏程') = 0 and INSTR(d.remark,'孙莉莉') = 0 and INSTR(d.remark,'李风明') = 0 and INSTR(d.remark,'付佳') = 0 and INSTR(d.remark,'曹孟恺') = 0 and INSTR(d.remark,'刘秀贞') = 0 and INSTR(d.remark,'张淑美') = 0 and INSTR(d.remark,'高谦') = 0 and INSTR(d.remark,'孙爱花') = 0 and INSTR(d.remark,'李晶') = 0 and INSTR(d.remark,'刘合花') = 0 and INSTR(d.remark,'高翠') = 0 and INSTR(d.remark,'周克荣') = 0 and INSTR(d.remark,'胡继雨') = 0 and INSTR(d.remark,'焦莹莹') = 0 and INSTR(d.remark,'吕乃安') = 0 and INSTR(d.remark,'李荣华') = 0 and INSTR(d.remark,'霍文青') = 0 and INSTR(d.remark,'韩萌') = 0 and INSTR(d.remark,'魏学') = 0 and INSTR(d.remark,'石玉') = 0 and INSTR(d.remark,'赵同倩') = 0 and INSTR(d.remark,'崔巍') = 0 and INSTR(d.remark,'朱明玉') = 0 and INSTR(d.remark,'张俊俊') = 0 and INSTR(d.remark,'陈惠芬') = 0 and INSTR(d.remark,'耿延平') = 0 and INSTR(d.remark,'张倩') = 0 and INSTR(d.remark,'胡月慧') = 0 and INSTR(d.remark,'周秀梅') = 0 and INSTR(d.remark,'金洋') = 0 and INSTR(d.remark,'王强') = 0 and INSTR(d.remark,'尚书霞') = 0 and INSTR(d.remark,'牛兆华') = 0 and INSTR(d.remark,'韩昌吉') = 0 and INSTR(d.remark,'赵文超') = 0 and INSTR(d.remark,'韩聪') = 0 and INSTR(d.remark,'李昕') = 0 and INSTR(d.remark,'李睿瑶') = 0 and INSTR(d.remark,'袁甜甜') = 0 and INSTR(d.remark,'李鲲鹏') = 0 and INSTR(d.remark,'康俊') = 0 and INSTR(d.remark,'王心怡') = 0 and INSTR(d.remark,'齐俊鑫') = 0 and INSTR(d.remark,'韩婷婷') = 0 and INSTR(d.remark,'马延鹏') = 0 and INSTR(d.remark,'隗霜') = 0 and INSTR(d.remark,'隗艳') = 0 and INSTR(d.remark,'李梦雨') = 0 and INSTR(d.remark,'燕双') = 0 and INSTR(d.remark,'吕红霞') = 0 and INSTR(d.remark,'时林') = 0 and INSTR(d.remark,'时光') = 0 and INSTR(d.remark,'景晓') = 0 and INSTR(d.remark,'马梦哲') = 0 and INSTR(d.remark,'孙哲') = 0 and INSTR(d.remark,'李晓迪') = 0)" +
                    " group by `浆员编号` desc ) dd order by dd.`最后采浆日期` desc",
                    new Object[] {DateUtil.format(dateRangeStart,"yyyyMMddHHmmsss"),DateUtil.format(dateRangeEnd,"yyyyMMddHHmmsss"),DateUtil.format(dateRangeStart,"yyyyMMddHHmmsss"),DateUtil.format(dateRangeEnd,"yyyyMMddHHmmsss"),name }), HashMap.class);

            Map data = new HashMap();
            data.put("name",name);
            data.put("dateRange",dateRange);
            data.put("today",DateUtil.format(new Date(), "yyyy-MM-dd"));
            data.put("donorList", donorList);hasRecordDonorList.addAll(donorList);mingxiaDonorList.addAll(donorList);
            data.put("count", donorList.size());
            tongjiList.add(data);
            if (donorList.size() == 0) {
                continue;
            }
            ManagerFactory.createExcel(root,
                    "excelTemplates/名下浆员名单.xls",
                    "\\宣传员\\宣传员-" + name + "-名下浆员名单（" + donorList.size() + "）.xls",data);

        }
        List<HashMap> allDonorList = taibangSqlManager.execute(new SQLReady("" +
                "select '所有' as '归属人',donor.donorNo 浆员编号, donor.donorName,donor.phone 联系电话, area.cname as 区域名称, donor.birthday as 生日, DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(donor.birthday)), '%Y')+0 AS 年龄, donor.sex, donor.codeDonorState as 状态,donor.donorName as 浆员姓名, DATE_FORMAT(donor.plasmaDate,'%Y-%m-%d')  as 最后采浆日期 from t_bims_donor as donor left join t_bims_code_area as area on area.ccode = donor.codeArea where donor.codeDonorState = 'N' and donor.plasmaDate between ? and ?  order by donor.plasmaDate desc"
                ,new Object[] {DateUtil.format(dateRangeStart,"yyyyMMddHHmmsss"), DateUtil.format(dateRangeEnd,"yyyyMMddHHmmsss"), }), HashMap.class);

        List shengyuList = new ArrayList();
        shengyuList.addAll(allDonorList);
        for (Iterator<Map<String, String>> it = hasRecordDonorList.iterator(); it.hasNext();) {
            Map<String, String> donor = it.next();
            String donorNo = donor.get("浆员编号");
            shengyuList.removeIf(allit->{
                String allDonorNo = (String)((Map)allit).get("浆员编号");
                if (StringUtils.equals(allDonorNo, donorNo)) {
                    return true;
                }
                return false;
            });
        }

        Map zilaidata = new HashMap();
        zilaidata.put("name","自来");
        zilaidata.put("dateRange",dateRange);
        zilaidata.put("today",DateUtil.format(new Date(), "yyyy-MM-dd"));
        zilaidata.put("donorList", shengyuList);
        zilaidata.put("count", shengyuList.size());
        tongjiList.add(zilaidata);
        ManagerFactory.createExcel(root,
                "excelTemplates/名下浆员名单.xls",
                "\\0自来-名下浆员名单（" + shengyuList.size() + "）.xls",zilaidata);

        Map quanbudata = new HashMap();
        quanbudata.put("name","全部");
        quanbudata.put("dateRange",dateRange);
        quanbudata.put("today",DateUtil.format(new Date(), "yyyy-MM-dd"));
        quanbudata.put("donorList", allDonorList);
        quanbudata.put("count", allDonorList.size());
        tongjiList.add(quanbudata);
        ManagerFactory.createExcel(root,
                "excelTemplates/名下浆员名单.xls",
                "\\0全部-名下浆员名单（" + allDonorList.size() + "）.xls",quanbudata);

        Map tongjidata = new HashMap();
        tongjidata.put("tongjiList",tongjiList);
        ManagerFactory.createExcel(root,
                "excelTemplates/名下浆员名单统计.xls",
                "\\0-名下浆员名单-统计.xls",tongjidata);

        mingxiaDonorList.addAll(shengyuList);
        Map mingxiaDonorData = new HashMap();
        mingxiaDonorData.put("dateRange",dateRange);
        mingxiaDonorData.put("today",DateUtil.format(new Date(), "yyyy-MM-dd"));
        mingxiaDonorData.put("donorList", mingxiaDonorList);
        mingxiaDonorData.put("count", mingxiaDonorList.size());

        ManagerFactory.createExcel(root,
                "excelTemplates/名下浆员名单（所有）.xls",
                "\\0-所有浆员名单（" + mingxiaDonorList.size() + "）.xls",mingxiaDonorData);
    }
}
